package com.dao;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import com.common.DBConn;
import com.entity.CustomerInfo;
import com.entity.PageModel;
import com.util.MD5;

public class CustomerInfoDAO {

	public static CustomerInfo getCustomerInfo(String regEmail) {

		String sql = "select * from CustomerInfo where email='" + regEmail
				+ "'";

		ArrayList<Object> list = DBConn.ExecuteQuery(sql, new CustomerInfo());
		return (CustomerInfo) list.get(0);

	}

	public void addCustomer(String regEmail, String regPwd, String question,
			String answer) {

		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

		String registerTime = sdf.format(new Date());
		String sql = "insert into CustomerInfo   (`email`, `pwd`, `registerTime`,`iscooled`, `question`, `answer`) values('"
				+ regEmail
				+ "','"
				+ MD5.MD5(regPwd)
				+ "','"
				+ registerTime
				+ "',0,'" + question + "','" + answer + "')";

		DBConn.ExecuteUD(sql);
	}

	public boolean checkEmail(String regEmail) {

		String sql = "select * from CustomerInfo where email='" + regEmail
				+ "'";

		ArrayList<Object> list = DBConn.ExecuteQuery(sql, new CustomerInfo());
		if (list != null) {
			if (list.size() == 1) {
				return true;
			}
		}
		return false;
	}

	public boolean checkLogin(String email, String pwd) {

		String sql = "select * from CustomerInfo where email = '" + email
				+ "' and pwd = '" + MD5.MD5(pwd) + "'";
		ArrayList<Object> list = DBConn.ExecuteQuery(sql, new CustomerInfo());
		if (list != null) {
			if (list.size() == 1) {
				return true;
			}
		}
		return false;

	}

	// 校验找加密码问题是否正确
	public boolean checklost(String email, String question, String answer) {

		String sql = "select * from CustomerInfo where email = '" + email
				+ "' and question = '" + question + "' and answer = '" + answer
				+ "'";
		ArrayList<Object> list = DBConn.ExecuteQuery(sql, new CustomerInfo());
		if (list != null) {
			if (list.size() == 1) {
				return true;
			}
		}
		return false;

	}

	// 密码找回功能设定密码为123456
	public void setdefaultpwd(String email) {

		// MD5.MD5("123456")
		String sql = "update CustomerInfo set pwd ='" + MD5.MD5("123456")
				+ "' where email='" + email + "'";

		DBConn.ExecuteUD(sql);
	}

	// 修改密码
	public void updatepwd(String email, String pwd) {

		String sql = "update CustomerInfo set pwd ='" + MD5.MD5(pwd)
				+ "' where email='" + email + "'";

		DBConn.ExecuteUD(sql);
	}

	public void coolCustomer(int customerId, int isCooled) {

		if (isCooled == 1) {
			isCooled = 0;
		} else {
			isCooled = 1;
		}

		String sql = "update CustomerInfo set isCooled = " + isCooled
				+ " where id=" + customerId;

		DBConn.ExecuteUD(sql);
	}
	/**
	 * 查询用户信息
	 * @param id
	 * @return
	 */
	public CustomerInfo getCustomerInfo(int id) {

		String sql = "select * from CustomerInfo where id=" + id;

		ArrayList<Object> list = DBConn.ExecuteQuery(sql, new CustomerInfo());

		if (list != null) {
			if (list.size() == 1) {

				return (CustomerInfo) list.get(0);
			}
		}
		return null;
	}
	/**
	 * 查询患者分页列表
	 * @param pm
	 * @return
	 */
	public ArrayList<Object> getPageModel(PageModel pm) {
		int everyPageCustomerInfoCount = pm.getEveryPageCount();
		int currentPage = pm.getCurrentPage();
		if (pm.getSumCount() % everyPageCustomerInfoCount != 0
				&& currentPage == pm.getLastPage()) {
			everyPageCustomerInfoCount = pm.getSumCount() % everyPageCustomerInfoCount;
		}
		String tsql = "SELECT * FROM CustomerInfo a ORDER BY a.registerTime DESC"
				+ " LIMIT " + (currentPage - 1) * pm.getPageSize() +","+currentPage * pm.getPageSize();
		return DBConn.ExecuteQuery(tsql, new CustomerInfo());
	}

	// 所有用户娄
	public int getSumCustomerCount() {

		String sql = "select * from CustomerInfo";

		ArrayList<Object> list = DBConn.ExecuteQuery(sql, new CustomerInfo());

		if (list != null) {

			return list.size();
		}
		return 0;
	}

	//冻结用户娄
	public int getdongjie() {

		String sql = "select * from CustomerInfo where iscooled=1";

		ArrayList<Object> list = DBConn.ExecuteQuery(sql, new CustomerInfo());

		if (list != null) {

			return list.size();
		}
		return 0;
	}

	// 正常用户娄
	public int getzhengchang() {

		String sql = "select * from CustomerInfo where iscooled=0";

		ArrayList<Object> list = DBConn.ExecuteQuery(sql, new CustomerInfo());

		if (list != null) {

			return list.size();
		}
		return 0;
	}

	// 搜索总数
	public int getSumSearchCustomerCount(String key) {

		String sql = "select * from CustomerInfo where email like  '%" + key
				+ "%'";

		ArrayList<Object> list = DBConn.ExecuteQuery(sql, new CustomerInfo());

		if (list != null) {

			return list.size();
		}
		return 0;
	}

	// 冻结用户娄
	public int getsdongjie(String key) {

		String sql = "select * from CustomerInfo where iscooled=1 and  email  like '%"
				+ key + "%'";

		ArrayList<Object> list = DBConn.ExecuteQuery(sql, new CustomerInfo());

		if (list != null) {

			return list.size();
		}
		return 0;
	}

	// 正常用户娄
	public int getszhengchang(String key) {

		String sql = "select * from CustomerInfo where iscooled=0 and email  like '%"
				+ key + "%'";

		ArrayList<Object> list = DBConn.ExecuteQuery(sql, new CustomerInfo());

		if (list != null) {

			return list.size();
		}
		return 0;
	}

	public boolean isCooled(String email) {

		String sql = "select * from CustomerInfo where email = '" + email + "'";

		ArrayList<Object> list = DBConn.ExecuteQuery(sql, new CustomerInfo());
		if (list != null) {
			if (list.size() == 1) {
				int isCooled = ((CustomerInfo) list.get(0)).getIsCooled();
				if (isCooled == 1) {
					return true;
				}
			}
		}
		return false;
	}

	public ArrayList<Object> searchCustomerInfo(PageModel pm, String key) {

		int everyPageCustomerInfoCount = pm.getEveryPageCount();

		int currentPage = pm.getCurrentPage();

		if (pm.getSumCount() % everyPageCustomerInfoCount != 0
				&& currentPage == pm.getLastPage()) {

			everyPageCustomerInfoCount = pm.getSumCount()
					% everyPageCustomerInfoCount;

		}

		String sql = "select * from (select * from (select   * from CustomerInfo where email like '%"
				+ key
				+ "%' order by id limit 0,"
				+ pm.getEveryPageCount()
				* pm.getCurrentPage()
				+ ") as a order by id desc limit 0, "
				+ everyPageCustomerInfoCount + " ) as b order by id";

		return DBConn.ExecuteQuery(sql, new CustomerInfo());
	}
}
